package com.example.universita.esami;

import java.util.ArrayList;
import java.util.List;
import java.io.*;


import android.os.Bundle;
import android.content.*;
import android.content.res.Configuration;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.annotation.SuppressLint;
import android.annotation.TargetApi;
import android.app.Activity;
import android.app.ProgressDialog;
import android.view.*;
import android.view.inputmethod.EditorInfo;
import android.widget.*;
import android.text.*;

public class DBDataUniversity {  

    SQLiteDatabase mDb;
    DbHelper mDbHelper;
    Context mContext;
    private static final String DB_NAME="iLibretto";
    private static final int DB_VERSION=1; 
    
    public DBDataUniversity(Context ctx){
            mContext=ctx;
            mDbHelper=new DbHelper(ctx, DB_NAME, null, DB_VERSION);   
    }
    
    public void open(){ 
            mDb=mDbHelper.getWritableDatabase();
            
    }
    
    public void close(){ 
            mDb.close();
    }
    

    private class DbHelper extends SQLiteOpenHelper {

    		private static final String CREATE_TABLE_FACULTY = "CREATE TABLE faculty (id integer primary key, name text not null);";
    		private static final String CREATE_TABLE_COURSE = "CREATE TABLE course (id integer primary key, name text not null, whatFaculty integer);";
    		private static final String CREATE_TABLE_SPECIAL = "CREATE TABLE special (id integer primary key, name text not null, whatCourse integer);";
    		private static final String CREATE_TABLE_EXAMS = "CREATE TABLE exams (id integer primary key autoincrement, name text not null, credits integer not null, semester integer not null, year integer not null, note text, data text, vote integer, id_prof integer);";
		    private static final String CREATE_TABLE_PROF = "CREATE TABLE docenti (id integer primary key autoincrement, name text not null, mail text, meeting text, phone integer);"; 
    		
            public DbHelper(Context context, String name, CursorFactory factory,int version) {
                    super(context, name, factory, version);
            }

            @Override
            public void onCreate(SQLiteDatabase _db) {
                    _db.execSQL(CREATE_TABLE_FACULTY);
                    _db.execSQL(CREATE_TABLE_COURSE);
                    _db.execSQL(CREATE_TABLE_SPECIAL);
                    _db.execSQL(CREATE_TABLE_EXAMS);
                    _db.execSQL(CREATE_TABLE_PROF);
            }

            @Override
            public void onUpgrade(SQLiteDatabase _db, int oldVersion, int newVersion) {
                  

            }

    }
            
    public void insertFaculty(int id, String name){
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("id", id);
		mDb.insert("faculty", null, cv);
	}
	
	public void insertCourse(int id, String name, int whatFaculty){
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("whatFaculty", whatFaculty);
		cv.put("id", id);
		mDb.insert("course", null, cv);
	}
	
	public void insertSpecial(int id, String name, int whatCourse){
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("whatCourse", whatCourse);
		cv.put("id", id);
		mDb.insert("special", null, cv);
	}
    
    public int convertIdItemInt(String str){
    	return Integer.parseInt(str.substring(0, str.indexOf(" ")-1));
    }
    
    public void loadToListFaculty(ArrayList<String> l){
    	Cursor c;
    	int nameCol;
    	
    	l.removeAll(l);
    	l.add("No Facolta'");
    	
    	c = mDb.rawQuery("SELECT * FROM faculty;", null);
    	
    	if(c.moveToFirst())
    		do{
    	        nameCol = c.getColumnIndex("name");
    			l.add(c.getString(nameCol));
    		}while(c.moveToNext());
    }
    
    public void loadToListCourse(ArrayList<String> l, int whatFaculty){
    	Cursor c;
    	int nameCol;
    	
    	l.removeAll(l);
    	l.add("No Corsi");
    	
    	c = mDb.rawQuery("SELECT * FROM course WHERE whatFaculty = "+ whatFaculty + ";", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("name");
    			l.add(c.getString(nameCol));
    		}while(c.moveToNext());
    }
    
    public void loadToListSpecial(ArrayList<String> l, int whatCourse){
    	Cursor c;
    	int nameCol;
    	
    	l.removeAll(l);
    	l.add("No Specializz.");
    	
    	c = mDb.rawQuery("SELECT * FROM special WHERE whatCourse = "+ whatCourse + ";", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("name");
    			l.add(c.getString(nameCol));
    		}while(c.moveToNext());
    }
    
    public int getIdFaculty(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM faculty where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("id");
    			return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int getIdCourse(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM course where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("id");
    			return c.getInt(nameCol);
    		}while(c.moveToNext());
    	
    	return 0;
    }
    
    public int getIdSpecial(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM special where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("id");
    			return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    	
    }
    
    public void deleteAllFaculty(){
        mDb.delete("faculty", null, null);
    }
    
    public void deleteAllCourse(){
        mDb.delete("course", null, null);
    }
    
    public void deleteAllSpecial(){
        mDb.delete("special", null, null);
    }
    
    public void insertExam(String name, int crediti, int anno, int semestre){
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("credits", crediti);
		cv.put("year", anno);
		cv.put("semester", semestre);
		mDb.insert("exams", null, cv);
	}
    
    public int getNameExam(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("credits");
    			return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return -1;
    	
    }
    
    public void deleteAllExams(){
        mDb.delete("exams", null, null);
    }
}